﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using NPOI.HSSF.UserModel; //xls格式(97-2003)
using NPOI.XSSF.UserModel; //xlsx格式(2007)
using NPOI.SS.UserModel;

// ReSharper disable once CheckNamespace
namespace Tzx.Utilities.Npoi
{
    /// <summary>
    /// Npoi导入(已兼容Excel97-2003和2007格式)
    /// </summary>
    public class NpoiImport
    {

        #region 构造

        /// <summary>
        /// 构造(默认97-2003格式)
        /// WinForm:using(FileStream fs = new FileStream(文件路径, FileMode.Open)){...}
        /// WebForm:Stream stream = FileUpload控件.PostedFile.InputStream;
        /// </summary>
        /// <param name="excelFileStream"></param>
        public NpoiImport(Stream excelFileStream)
            : this(excelFileStream, false)
        {
            //
        }

        /// <summary>
        /// 构造
        /// WinForm:using(FileStream fs = new FileStream(文件路径, FileMode.Open)){...}
        /// WebForm:Stream stream = FileUpload控件.PostedFile.InputStream;
        /// </summary>
        /// <param name="excelFileStream"></param>
        /// <param name="isExcel2007">是否为Excel2007格式</param>
        public NpoiImport(Stream excelFileStream, bool isExcel2007)
        {
            this._excelFileStream = excelFileStream;
            this._isExcel2007 = isExcel2007;
        }

        /// <summary>
        /// 源Excel文件流
        /// </summary>
        public Stream ExcelFileStream
        {
            get { return _excelFileStream; }
        }
        private Stream _excelFileStream;

        /// <summary>
        /// 是否为Excel2007格式,否则97-2003版本
        /// </summary>
        protected bool IsExcel2007
        {
            get { return _isExcel2007; }
        }
        private bool _isExcel2007 = false;

        #endregion

        /// <summary>
        /// 导入(Excel流=>DataTable,全部Index参数从0开始)
        /// </summary>
        /// <param name="sheetIndex">工作表索引(从0开始)</param>
        /// <param name="headerRowIndex">从第几行开始(若第一行为标题要从第二行开始,则传入1)</param>
        /// <returns></returns>
        public DataTable ExcelStreamToDataTable(int sheetIndex, int headerRowIndex)
        {
            return NpoiUtilities.ExcelStreamToDataTable(ExcelFileStream, sheetIndex, headerRowIndex, IsExcel2007);
        }

        /// <summary>
        /// 导入(Excel流=>DataTable,全部Index参数从0开始)
        /// </summary>
        /// <param name="sheetName">工作表名称</param>
        /// <param name="headerRowIndex">从第几行开始(若第一行为标题要从第二行开始则传入1)</param>
        /// <returns></returns>
        public DataTable ExcelStreamToDataTable(string sheetName, int headerRowIndex)
        {
            //HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
            //int index = workbook.GetNameIndex(sheetName);
            //return ExcelStreamToDataTable(index, headerRowIndex, isExcel2007);

            IWorkbook workbook ;
            if(IsExcel2007)
            {
                workbook = new HSSFWorkbook(ExcelFileStream);
            }
            else
            {
                workbook = new XSSFWorkbook(ExcelFileStream);
            }
            int index = workbook.GetNameIndex(sheetName);
            return ExcelStreamToDataTable(index, headerRowIndex);
        }

        /// <summary>
        /// 从Excel流生成新的DataTable
        /// </summary>
        /// <param name="sheetIndex"></param>
        /// <param name="headIndex"></param>
        /// <returns></returns>
        public DataTable ImportFromStream(int sheetIndex, int headIndex)
        {
            DataTable table = ExcelStreamToDataTable(sheetIndex, headIndex);
            //todo:excelStream.Close();
            return table;
        }

    }
}